package model.dba;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.List;

import model.Alumno;
import model.Area;
import model.Contacto;
import model.Curso;
import model.PersonalAdministrativo;

public class ContactoDAO extends JDBC {
	private static ContactoDAO contactoDAO;

	private ContactoDAO() {
		super();
	}

	public static ContactoDAO getInstance() {
		if (contactoDAO == null) {
			contactoDAO = new ContactoDAO();
		}
		return contactoDAO;
	}

	public int add(Contacto contacto) {

		try {
			String queryString = "INSERT INTO contactos(fecha, descripcion, dniPersonalAdmin, idArea";

			Curso curso = contacto.getCursos() != null ? contacto.getCursos().get(0) : null;
			if (curso != null) {
				String subQ = ", idCurso, idAlumno) VALUES( ?, ?, ?, ?, ?, ?)";
				preparedStatement = connection.prepareStatement(queryString + subQ);
				preparedStatement.setInt(5, curso != null ? (curso.getId() != null ? curso.getId() : 6) : 6);
				preparedStatement.setInt(6, contacto.getAlumno().getId());

			} else {
				String subQ = "idCurso, idAlumno) VALUES(  ?,?, ?, ?, ?, ?)";
				preparedStatement = connection.prepareStatement(queryString + subQ);
				preparedStatement.setInt(5, 6);
				preparedStatement.setInt(6, contacto.getAlumno().getId());
			}
			preparedStatement.setTimestamp(1, new Timestamp(contacto.getFecha().getTimeInMillis()));
			preparedStatement.setString(2, contacto.getDescripcion());
			preparedStatement.setString(3, contacto.getPersonalAdministrativo() != null ? contacto.getPersonalAdministrativo().getDNI() : null);
			Area area = contacto.getAreas() != null ? contacto.getAreas().get(0) : null;
			preparedStatement.setInt(4, area != null ? (area.getId() != null ? area.getId() : 0) : 0);

			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return getLastId();

	}

	public boolean update(Contacto contacto) {
		boolean state = false;
		try {
			String queryString = "UPDATE contactos SET   fecha=?,  descripcion=?,  dniPersonalAdmin=?,  idArea=?,  idCurso=?,  idAlumno=? WHERE idContacto=?";

			preparedStatement = connection.prepareStatement(queryString);

			preparedStatement.setTimestamp(1, new Timestamp(contacto.getFecha().getTimeInMillis()));
			preparedStatement.setString(2, contacto.getDescripcion());
			preparedStatement.setString(3, contacto.getPersonalAdministrativo().getDNI());
			Area area = contacto.getAreas().get(0);
			preparedStatement.setInt(4, area != null ? area.getId() : null);
			Curso curso = contacto.getCursos().get(0);
			preparedStatement.setInt(5, curso != null ? curso.getId() : null);
			preparedStatement.setInt(6, contacto.getAlumno().getId());
			preparedStatement.setInt(7, contacto.getId());

			state = preparedStatement.executeUpdate() != 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();

			}
		}
		return state;
	}

	public boolean delete(Contacto contacto) {
		boolean state = false;
		try {
			String queryString = "DELETE FROM contactos WHERE idContacto=?";

			preparedStatement = connection.prepareStatement(queryString);
			preparedStatement.setInt(1, contacto.getId());
			state = preparedStatement.executeUpdate() != 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return state;

	}

	public Contacto get(Integer id) {

		Contacto contacto = new Contacto();
		try {
			String queryString = "SELECT * FROM contactos where idContacto = '" + id + "';";
			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				contacto = populate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return contacto;
	}

	public int getLastId() {
		int ret = 0;
		try {
			String queryString = "SELECT idContacto FROM contactos order by idContacto DESC LIMIT 1  ";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				ret = resultSet.getInt("idContacto");

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return ret;

	}

	public List<Contacto> getAll() {
		List<Contacto> contactoList = new ArrayList<Contacto>();

		try {
			String queryString = "SELECT * FROM contactos ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				contactoList.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return contactoList;
	}

	public List<Contacto> get(Alumno alumno) {
		List<Contacto> contactoList = new ArrayList<Contacto>();

		try {
			String queryString = "SELECT * FROM contactos where idAlumno = " + alumno.getId() + " ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				contactoList.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return contactoList;
	}

	public List<Contacto> get(PersonalAdministrativo personalAdministrativo) {
		List<Contacto> contactoList = new ArrayList<Contacto>();

		try {
			String queryString = "SELECT * FROM contactos where dniPersonalAdmin = '" + personalAdministrativo.getDNI() + "' ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				contactoList.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return contactoList;
	}

	private Contacto populate() throws SQLException {
		CursoDAO cursoDAO = CursoDAO.getInstance();
		AreaDAO areaDAO = AreaDAO.getInstance();
		PersonalAdministrativoDAO personalAdministrativoDAO = PersonalAdministrativoDAO.getInstance();
		AlumnoDAO alumnoDAO = AlumnoDAO.getInstance();

		List<Curso> cursos = new ArrayList<Curso>();
		cursos.add(cursoDAO.get(new Integer((int) resultSet.getLong("idCurso"))));
		List<Area> areas = new ArrayList<Area>();
		areas.add(areaDAO.get((int) resultSet.getLong("idArea")));

		Contacto contacto = new Contacto();
		contacto.setId((int) resultSet.getLong("idContacto"));
		GregorianCalendar calendar = new GregorianCalendar();
		calendar.setTime(resultSet.getTimestamp("fecha"));
		contacto.setFecha(calendar);
		contacto.setDescripcion(resultSet.getString("descripcion"));
		contacto.setPersonalAdministrativo(personalAdministrativoDAO.get(resultSet.getString("dniPersonalAdmin")));
		contacto.setAreas(areas);
		contacto.setAlumno(alumnoDAO.getAlumno((int) resultSet.getLong("idAlumno")));

		return contacto;

	}
}